package forum;
import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import javax.sql.*;
import javax.naming.*;

public class db_code {
	
	public Connection conn;
	
	public static Connection connect(){
		try {
			Context initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");
			DataSource ds = (DataSource) envCtx.lookup("jdbc/forum");
			//System.out.println("Found the name in JSNL!");
			return  ds.getConnection();
		} catch (Exception e) {
			System.out.println("ERROOOOORRR!!! Could not connect!");
			e.printStackTrace();
			return null;
		}
	}
	/*
	public void ConnectOld(){
		try {
			Context initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");
			DataSource ds = (DataSource) envCtx.lookup("jdbc/forum");
			System.out.println("Found the name in JSNL!");
			conn = ds.getConnection();
		} catch (Exception e) {
			System.out.println("ERROOOOORRR!!! Could not connect!");
			e.printStackTrace();
		}
	}
	*/
	
	public static void disconnect(Connection conn){
		try {
			conn.close();
		} catch (Exception e) {
		}
	}
	
	public void test(){
		Connection conn = connect();
		
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			String query = "SELEC * FROM user";
			rs = stmt.executeQuery(query);
			while(rs.next()){
				String name = (String)rs.getObject("name");
				String email = (String)rs.getObject("email");
				System.out.println(name);
				System.out.println(email);
			}
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		disconnect(conn);
	}
	
	
	/**
	 * Calls the DB and tries to login.
	 * @param username
	 * @param password
	 * @return
	 */
	public boolean db_login(String username, String password){
		try{
			Connection conn = connect();
			
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			String query = "SELECT * FROM user WHERE email = '" + username +"' AND password = '" + password + "' LIMIT 1;";
			rs = stmt.executeQuery(query);
			if( rs == null){
				System.out.println("LOGIN FAIL! FOUND NO SUCH USER//PASSWORD");
			}
			while(rs.next()){
				if(rs.wasNull()){
					System.out.println("LOGIN FAIL! FOUND NO SUCH USER//PASSWORD");
					disconnect(conn);
					return false;
				}
				else{
					//Good login!
					System.out.println("Good login!");
					
					disconnect(conn);
					return true;
				}
			}
			return false;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return false;
	}
	
	
	public User getUser(String username, String password){
		Connection conn = connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query;
			query = "SELECT * FROM user WHERE email = '" + username + "' AND password = '" + password + "' LIMIT 1;";
			
			rs = stmt.executeQuery(query);
			
			User row = null;
			while(rs.next()){
				row  = new User(
						rs.getInt("userID"),
						rs.getInt("userlvl"),
						rs.getString("name"),
						rs.getString("email"),
						rs.getString("password"),
						rs.getTimestamp("joined"),
						rs.getInt("numberOfPosts")
						);
			}
			disconnect(conn);
			return row;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
	}
	
	
	public boolean createUser(int userlvl, String name, String email, String password, Date joined, int numberOfPosts){
		try {
			
			Statement stmt = conn.createStatement();
			String query = "INSERT INTO user (userlvl, name, email, password, joined, numberOfPosts)" +
					"VALUES(" + 
						userlvl + ",'" + 
						name + "','" + 
						email + "','" + 
						password + "','" + 
						joined + "'," + 
						numberOfPosts + ");";
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! INSERT!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean editUser(int userID, int userlvl, String name, String email, String password, Date joined, int numberOfPosts){
		try {
			Statement stmt = conn.createStatement();
			String query = "UPDATE user " +
					"SET " +
						"userlvl = " + userlvl + ","+
						"name = '" + name + "',"+
						"email = '" + email + "'"+
						"password = '" + password + "'"+
						"joined = '" + joined + "'"+
						"numberOfPosts = " + numberOfPosts +
					"WHERE " +
						"userID = " + userID;
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! UPDATE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean deleteUser(int userID){
		try {
			Statement stmt = conn.createStatement();
			String query = "DELETE FROM user WHERE userID = " + userID;
			
			return stmt.execute(query);
		} catch (Exception e) {
			System.out.println("FAIL! DELETE!");
			e.printStackTrace();
			return false;
		}
	}
	
	
	public boolean createPM(int from, int to, Date date, String msg){
		try {
			
			Statement stmt = conn.createStatement();
			String query = "INSERT INTO pm (from, to, date, message)" +
					"VALUES(" + 
						from + "," + 
						to + ",'" + 
						date + "','" + 
						msg + "');";
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! INSERT!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean editPM(int pmID, int from, int to, Date date, String message){
		try {
			Statement stmt = conn.createStatement();
			String query = "UPDATE pm " +
					"SET " +
						"from = " + from + "," +
						"to = " + to + ","+
						"date = '" + date + "',"+
						"message = '" + message + "'"+
					"WHERE " +
						"pmID = " + pmID;
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! UPDATE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean deletePM(int pmID){
		try {
			Statement stmt = conn.createStatement();
			String query = "DELETE FROM pm WHERE pmID = " + pmID;
			
			return stmt.execute(query);
		} catch (Exception e) {
			System.out.println("FAIL! DELETE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean createSubforum(String name, int numberOfThreads, Date createdOn, int minimumRestrictionlvl){
		try {
			
			Statement stmt = conn.createStatement();
			String query = "INSERT INTO subforum (name, numberOfThreads, createdOn, minimumRestrictionlvl)" +
					"VALUES('" + 
						name + "'," + 
						numberOfThreads + ",'" + 
						createdOn + "'," + 
						minimumRestrictionlvl + ");";
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! INSERT!");
			e.printStackTrace();
			return false;
		}
	}
	
	public SubForum getSubforum(int subForumID){
		try {
			Connection conn = connect();
			Statement stmt = conn.createStatement();
			String query = "SELECT * FROM subforum WHERE subForumID = " + subForumID+" LIMIT 1;"; 
			
			ResultSet rs = stmt.executeQuery(query);
			
			SubForum row = null;
			while(rs.next()){
				row  = new SubForum(
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("numberOfThreads"), 
						(Timestamp)rs.getObject("createdOn"),
						(Integer)rs.getObject("minimumRestrictionlvl"));
			}
			disconnect(conn);
			return row;
		} catch (Exception e) {
			disconnect(conn);
			System.out.println("FAIL! UPDATE!");
			e.printStackTrace();
			return null;
		}
	}
	
	
	public boolean editSubforum(int subForumID, String name, int numberOfThreads, Date createdOn, int minimumRestrictionlvl){
		try {
			Statement stmt = conn.createStatement();
			String query = "UPDATE subforum " +
					"SET " +
						"name = '" + name + "'," +
						"numberOfThreads = " + numberOfThreads + "," +
						"createdOn = '" + createdOn +"',"+
						"minimumRestrictionlvl = " + minimumRestrictionlvl +
					"WHERE " +
						"subForumID = " + subForumID;
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			
			System.out.println("FAIL! UPDATE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean deleteSubForum(int subForumID){
		try {
			Statement stmt = conn.createStatement();
			String query = "DELETE FROM subforum WHERE subForumID = " + subForumID;
			
			return stmt.execute(query);
		} catch (Exception e) {
			System.out.println("FAIL! DELETE!");
			e.printStackTrace();
			return false;
		}
	}
	
	
	public boolean createThread(int subForumID, String name, int createdBy, Date createdOn,int views, int replies){
		try {
			connect();
			Statement stmt = conn.createStatement();
			String query = "INSERT INTO thread (subForumID, name, createdBy, createdOn, views, replies)" +
					"VALUES(" + 
						subForumID + ",'" + 
						name + "'," + 
						createdBy + ",'" + 
						createdOn + "'," + 
						views + "," +
						replies + ");";
			stmt.execute(query);
			return true;
			
		} catch (Exception e) {
			System.out.println("FAIL! INSERT!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean editThread(int threadID, int subForumID, String name, int createdBy, Date createdOn, int views, int replies){
		try {
			Statement stmt = conn.createStatement();
			String query = "UPDATE thread " +
					"SET " +
						"subForumID = " + subForumID + "," +
						"name = '" + name + "'," +
						"createdBy = " + createdBy +","+
						"createdOn = '" + createdOn + "',"+
						"views = " + views + ","+
						"replies = " + replies +
					"WHERE " +
						"threadID = " + threadID;
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! INSERT!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean deleteThread(int threadID){
		try {
			Statement stmt = conn.createStatement();
			String query = "DELETE FROM thread WHERE threadID = " + threadID;
			
			return stmt.execute(query);
		} catch (Exception e) {
			System.out.println("FAIL! DELETE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean createPost(int threadID, int userID, String content){
		java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
		return createPost(threadID,userID,content,now,now);
	}
	
	public boolean createPost(int threadID, int userID, String content, Date createdOn, Date lasteditedOn){
		try {
			
			Statement stmt = conn.createStatement();
			String query = "INSERT INTO post (threadID, userID, content, createdOn , lasteditedOn)" +
					"VALUES(" + 
						threadID + "," + 
						userID + ",'" + 
						content + "','" + 
						createdOn + "','" + 
						lasteditedOn + "');";
			stmt.execute(query);
			return true;
		} catch (Exception e) {

			System.out.println("FAIL! INSERT!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean editPost(int postID, int threadID, int userID, String content, Date createdOn, Date lasteditedOn){
		try {
			connect();
			Statement stmt = conn.createStatement();
			String query = "UPDATE post " +
					"SET " +
						"threadID = " + threadID + "," +
						"userID = " + userID + ","+
						"content = '" + content + "',"+
						"createdOn = '" + createdOn + "',"+
						"lasteditedOn = '" + lasteditedOn +"'"+
					"WHERE " +
						"postID = " + postID;
			
			return stmt.execute(query);
			
		} catch (Exception e) {
			System.out.println("FAIL! UPDATE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean deletePost(int postID){
		try {
			Statement stmt = conn.createStatement();
			String query = "DELETE FROM post WHERE postID = " + postID;
			
			return stmt.execute(query);
		} catch (Exception e) {
			System.out.println("FAIL! DELETE!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean incrementThreadReplies(int ThreadId){
		return incrementThread(ThreadId, true);
	}
	
	public boolean incrementThreadViews(int ThreadId){
		return incrementThread(ThreadId, false);
	}
	
	public boolean incrementThread(int id, boolean replies){
		connect();
		try{
			Statement stmt = conn.createStatement();
			String query;
			
			if(replies)
				query= "UPDATE thread SET replies=replies+1 WHERE threadID = " + id;
			else
				query= "UPDATE thread SET views=views+1 WHERE threadID = " + id;
			 
			return stmt.execute(query);
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean incrementNumberOfThreadsInSubForum(int subForumID){
		try{
			Statement stmt = conn.createStatement();
			String query = "UPDATE subforum SET numberOfThreads=numberOfThreads+1 WHERE subForumID = " + subForumID;
			return stmt.execute(query);
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean incrementNumberOfPostsOnAUser(int userId){
		try{
			Statement stmt = conn.createStatement();
			String query = "UPDATE user SET numberOfPosts=numberOfPosts+1 WHERE userID = " + userId;
			return stmt.execute(query);
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
			return false;
		}
	}
	
	public ArrayList<User> getAllUsers(){
		Connection conn = connect();
		ArrayList<User> result = new ArrayList<User>();
		
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			String query = "SELECT * FROM user";
			
			rs = stmt.executeQuery(query);
			
			User row;
			while(rs.next()){
				row  = new User(
						(Integer)rs.getObject("userID"), 
						(Integer)rs.getObject("userlvl"), 
						(String)rs.getObject("name"), 
						(String)rs.getObject("email"),
						(String)rs.getObject("password"),
						(Timestamp)rs.getObject("joined"),
						(Integer)rs.getObject("numberOfPosts"));
				result.add(row);
			}
			disconnect(conn);
			return result;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<SubForum> getAllSubForums(){
		conn = connect();
		ArrayList<SubForum> result = new ArrayList<SubForum>();
		
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			String query = "SELECT * FROM subforum";
			
			rs = stmt.executeQuery(query);
			
			SubForum row;
			while(rs.next()){
				row  = new SubForum(
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("numberOfThreads"), 
						(Timestamp)rs.getObject("createdOn"),
						(Integer)rs.getObject("minimumRestrictionlvl"));
				result.add(row);
			}
			disconnect(conn);
			return result;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
		
	}
	
	/**
	 * Fetches all the PMs that are send to the user with userId
	 * @return a Arraylist<PM> object, that can be null i think...
	 */
	public ArrayList<PM> getAllPMsToAUser(int userId){
		return getAllPMs(userId,true);
	} 
	
	/**
	 * Fetches all the PMs that the user with userID have sent.
	 * @return a Arraylist<PM> object, that can be null i think...
	 */
	public ArrayList<PM> getAllPMsSentByAUser(int userId){
		return getAllPMs(userId,false);
	} 
	
	/**
	 * @param userId - the UserID of the user
	 * @param ToTheUser - true if we are fetching PMS TO the user, false if we are fetching PMS FROM the user.
	 */
	private ArrayList<PM> getAllPMs(int userId, boolean ToTheUser){
		connect();
		
		ArrayList<PM> result = new ArrayList<PM>();
		
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query;
			
			if(ToTheUser)
				query = "SELECT * FROM pm WHERE to = " + userId;
			else
				query = "SELECT * FROM pm WHERE from = " + userId;
			
			
			rs = stmt.executeQuery(query);
			
			PM row;
			while(rs.next()){
				row  = new PM(
						(Integer)rs.getObject("pmID"),
						(Integer)rs.getObject("from"), 
						(Integer)rs.getObject("to"), 
						(Timestamp)rs.getObject("date"), 
						(String)rs.getObject("message"));
				result.add(row);
			}
			return result;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
	} 
	
	
	public ArrayList<Thread> getAllThreadsByUser(int userID){
		return getAllThreads(userID, true);
	}
	
	public ArrayList<Thread> getAllThreadsBySubForum(int subForumID){
		return getAllThreads(subForumID, false);
	}
	
	private ArrayList<Thread> getAllThreads(int id, boolean byUser){
		connect();
		
		ArrayList<Thread> result = new ArrayList<Thread>();
		
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query;
			
			if(byUser)
				query = "SELECT * FROM thread WHERE createdBy = " + id;
			else
				query = "SELECT * FROM thread WHERE subForumID = " + id;
			
			rs = stmt.executeQuery(query);
			
			Thread row;
			while(rs.next()){
				row  = new Thread(
						(Integer)rs.getObject("threadID"), 
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("createdBy"), 
						(Timestamp)rs.getObject("createdOn"), 
						(Integer)rs.getObject("views"), 
						(Integer)rs.getObject("replies"));
				result.add(row);
			}
			//disconnect();
			return result;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<Post> getAllPostsFromAUser(int userId){
		return getAllPosts(userId, true);
	}

	public ArrayList<Post> getAllPostsInAThread(int threadId){
		return getAllPosts(threadId, false);
	}
	
	private ArrayList<Post> getAllPosts(int id, boolean byUser){
		connect();
		ArrayList<Post> result = new ArrayList<Post>();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query;
			if(byUser)
				 query = "SELECT * FROM post WHERE userID = " + id;
			else
				query = "SELECT * FROM post WHERE threadID = " + id;
			
			rs = stmt.executeQuery(query);
			
			Post row;
			while(rs.next()){
				row  = new Post(
						(Integer)rs.getObject("postID"), 
						(Integer)rs.getObject("threadID"), 
						(Integer)rs.getObject("userID"), 
						(String)rs.getObject("content"), 
						(Timestamp)rs.getObject("createdOn"), 
						(Timestamp)rs.getObject("lasteditedOn"));
				result.add(row);
			}
			//disconnect();
			return result;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
	}
	
	
	public int getThreadID(){
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SHOW TABLE STATUS LIKE 'thread'";
			
			rs = stmt.executeQuery(query);
			
			BigInteger bigint = new BigInteger("-1");
			while(rs.next()){
				
				bigint = new BigInteger(""+rs.getInt("Auto_increment")); 
			}
			//disconnect();
			
			return bigint.intValue();
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return -1;
	}
	
	public int getPostID(){
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SHOW TABLE STATUS LIKE 'post'";
			
			rs = stmt.executeQuery(query);
			
			int id = -1;
			while(rs.next()){
				id = (Integer)rs.getObject("Auto_increment"); 
			}
			//disconnect();
			return id;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return -1;
	}
	
	public forum.Thread getThread(int threadID){
		
		Connection conn = connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SELECT * FROM thread WHERE threadID = " + threadID + " LIMIT 1";
			
			rs = stmt.executeQuery(query);
			
			Thread t = null;
			while(rs.next()){
				
				t = new Thread(rs.getInt("threadID"), rs.getInt("subForumID"), rs.getString("name"), rs.getInt("createdBy"), rs.getTimestamp("createdOn"), rs.getInt("views"), rs.getInt("replies"));		
			}
			disconnect(conn);
			return t;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return null;
		
	}
	
	
	public String getUserName(int userID){
		
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SELECT email FROM user WHERE userID = " + userID + " LIMIT 1";
			
			rs = stmt.executeQuery(query);
			
			String email = "";
			while(rs.next()){
				email = (String)rs.getObject("email"); 
			}
			//disconnect();
			return email;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return "";
	}
	
	
	public int getUserID(){
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SHOW TABLE STATUS LIKE 'user'";
			
			rs = stmt.executeQuery(query);
			
			int id = -1;
			while(rs.next()){
				id = (Integer)rs.getObject("Auto_increment"); 
			}
			//disconnect();
			return id;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return -1;
	}
	
	public int getPMID(){
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SHOW TABLE STATUS LIKE 'pm'";
			
			rs = stmt.executeQuery(query);
			
			int id = -1;
			while(rs.next()){
				id = (Integer)rs.getObject("Auto_increment"); 
			}
			//disconnect();
			return id;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return -1;
	}
	
	public int getSubForumID(){
		connect();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			
			String query = "SHOW TABLE STATUS LIKE 'subforum'";
			
			rs = stmt.executeQuery(query);
			
			int id = -1;
			while(rs.next()){
				id = (Integer)rs.getObject("Auto_increment"); 
			}
			//disconnect();
			return id;
		}
		catch(SQLException e){
			System.out.println("SQL statement error!");
			e.printStackTrace();
		}
		return -1;
	}
}
